﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using NPOI.SS.UserModel;
using NPOI.HSSF.UserModel;
using NPOI.XSSF.UserModel;
using System.IO;

namespace seo_tool.lib
{
    class Excel
    {
        public static List<string> Read(string fileName)
        {
            List<string> list = new List<string>();
            IWorkbook workbook = null;  //新建IWorkbook对象
            FileStream fileStream = new FileStream(fileName, FileMode.Open, FileAccess.Read);
            if (fileName.IndexOf(".xlsx") > 0) // 2007版本
            {
                workbook = new XSSFWorkbook(fileStream);  //xlsx数据读入workbook
            }
            else if (fileName.IndexOf(".xls") > 0) // 2003版本
            {
                workbook = new HSSFWorkbook(fileStream);  //xls数据读入workbook
            }
            ISheet sheet = workbook.GetSheetAt(0);  //获取第一个工作表
            IRow row;// = sheet.GetRow(0);            //新建当前工作表行数据
            for (int i = 1; i < sheet.LastRowNum; i++)  //对工作表每一行
            {
                row = sheet.GetRow(i);   //row读入第i行数据
                if (row != null)
                {
                    string cellValue = row.GetCell(0).ToString();
                    list.Add(cellValue);
                }
            }
            fileStream.Close();
            workbook.Close();
            return list;
        }

        public static string Write(string fileName, System.Data.SQLite.SQLiteDataReader reader)
        {
            IWorkbook workbook = new XSSFWorkbook();
            ISheet sheet = workbook.CreateSheet("收录数据");
            IRow row = sheet.CreateRow(0);
            row.CreateCell(0).SetCellValue("URL");
            row.CreateCell(1).SetCellValue("标题");
            row.CreateCell(2).SetCellValue("是否收录");
            row.CreateCell(3).SetCellValue("索引");
            row.CreateCell(4).SetCellValue("平台");

            int i = 0;
            while (reader.Read())
            {
                i++;
                row = sheet.CreateRow(i);
                row.CreateCell(0).SetCellValue(reader["url"].ToString());
                row.CreateCell(1).SetCellValue(reader["title"].ToString());
                row.CreateCell(2).SetCellValue(reader["is_shoulu"].ToString());
                row.CreateCell(3).SetCellValue(reader["suoyin"].ToString());
                row.CreateCell(4).SetCellValue(reader["search"].ToString());
            }

            using (FileStream fs = File.OpenWrite(fileName))
            {
                workbook.Write(fs);
            }
            workbook.Close();
            return "成功保存到："+fileName;
        }

        public static string Write_xg(string fileName, System.Data.SQLite.SQLiteDataReader reader)
        {
            IWorkbook workbook = new XSSFWorkbook();
            ISheet sheet = workbook.CreateSheet("相关词数据");
            IRow row = sheet.CreateRow(0);
            row.CreateCell(0).SetCellValue("ID");
            row.CreateCell(1).SetCellValue("关键词");
            row.CreateCell(2).SetCellValue("PID");
            row.CreateCell(3).SetCellValue("级别");
            row.CreateCell(4).SetCellValue("下拉词");
            row.CreateCell(5).SetCellValue("相关词");
            row.CreateCell(6).SetCellValue("平台");

            int i = 0;
            while (reader.Read())
            {
                i++;
                row = sheet.CreateRow(i);
                row.CreateCell(0).SetCellValue(reader["id"].ToString());
                row.CreateCell(1).SetCellValue(reader["keyword"].ToString());
                row.CreateCell(2).SetCellValue(reader["pid"].ToString());
                row.CreateCell(3).SetCellValue(reader["level"].ToString());
                row.CreateCell(4).SetCellValue(reader["xiala"].ToString());
                row.CreateCell(5).SetCellValue(reader["xiangguan"].ToString());
                row.CreateCell(6).SetCellValue(reader["search"].ToString());
            }

            using (FileStream fs = File.OpenWrite(fileName))
            {
                workbook.Write(fs);
            }
            workbook.Close();
            return "成功保存到：" + fileName;
        }


    }
}
